package com.ssyt.hiring.company.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.ssyt.hiring.common.PageModel;
import com.ssyt.hiring.common.util.DBUtil;
import com.ssyt.hiring.company.entity.PositionEntity;

public class PositionService {

	/**
	 * 查询所有的职位信息
	 * 
	 * @param searchText
	 * @param pageSize
	 * @param pageNo
	 * 
	 * 
	 * @return
	 */
	public PageModel<PositionEntity> listPosition(String searchText,
			int pageNo, int pageSize) {
		// List<PositionEntity> positionList = new ArrayList<>();
		// StringBuilder sql = new StringBuilder(
		// "SELECT p.*, c.name AS 'company$name', c.address AS 'company$address', c.slogan AS 'company$slogan' FROM position p, company c WHERE c.id=p.company_id ");
		// if (StringUtils.isNotBlank(searchText)) {
		// sql.append("AND (p.name LIKE ? OR c.name LIKE ?) ");
		// }
		// sql.append("ORDER BY id DESC LIMIT ?, ?");
		// Connection connection = DBUtil.getConnection();
		// PreparedStatement preparedStatement = null;
		// ResultSet resultSet = null;
		// try {
		// preparedStatement = connection.prepareStatement(sql.toString());
		// if (StringUtils.isNotBlank(searchText)) {
		// preparedStatement.setString(1, "%" + searchText + "%");
		// preparedStatement.setString(2, "%" + searchText + "%");
		// preparedStatement.setInt(3, (pageNo - 1) * pageSize);
		// preparedStatement.setInt(4, pageSize);
		// } else {
		// preparedStatement.setInt(1, (pageNo - 1) * pageSize);
		// preparedStatement.setInt(2, pageSize);
		// }
		// resultSet = preparedStatement.executeQuery();
		// while (resultSet.next()) {
		// PositionEntity position = new PositionEntity();
		// position.setName(resultSet.getString("name"));
		// position.setSalary1(resultSet.getInt("salary1"));
		// position.setSalary2(resultSet.getInt("salary2"));
		// position.setReleaseTime(resultSet.getTimestamp("release_time"));
		// position.setCompany$name(resultSet.getString("company$name"));
		// position.setCompany$address(resultSet
		// .getString("company$address"));
		// position.setCompany$slogan(resultSet
		// .getString("company$slogan"));
		// positionList.add(position);
		// }
		// } catch (SQLException e) {
		// e.printStackTrace();
		// } finally {
		// DBUtil.close(resultSet, preparedStatement, connection);
		// }
		//
		// return new PageModel<PositionEntity>(positionList,
		// getTotalRecords(searchText), pageNo, pageSize);

		List<Object> paramList = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder(
				"SELECT p.*, c.name AS 'company$name', c.address AS 'company$address', c.slogan AS 'company$slogan' FROM position p, company c WHERE c.id=p.company_id ");
		if (StringUtils.isNotBlank(searchText)) {
			sb.append("AND (p.name LIKE ? OR c.name LIKE ?) ");
			paramList.add("%" + searchText + "%");
			paramList.add("%" + searchText + "%");
		}
		sb.append("ORDER BY p.id DESC LIMIT ?, ?");
		paramList.add((pageNo - 1) * pageSize);
		paramList.add(pageSize);
		return DBUtil.executeQueryByPage(PositionEntity.class, pageNo,
				pageSize, sb.toString(), paramList);
	}

	/**
	 * 查询总共有多少条职位
	 * 
	 * @param searchText
	 * @return
	 */
	private int getTotalRecords(String searchText) {
		int totalRecords = 0;
		StringBuilder sql = new StringBuilder(
				"SELECT COUNT(*) AS 'totalRecords' FROM position p, company c WHERE c.id=p.company_id ");
		if (StringUtils.isNotBlank(searchText)) {
			sql.append("AND (p.name LIKE ? OR c.name LIKE ?)");
		}
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql.toString());
			if (StringUtils.isNotBlank(searchText)) {
				preparedStatement.setString(1, "%" + searchText + "%");
				preparedStatement.setString(2, "%" + searchText + "%");
			}
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			totalRecords = resultSet.getInt("totalRecords");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet, preparedStatement, connection);
		}
		return totalRecords;
	}
}
